package com.demo.dao.impl;

import com.demo.util.Util;
import com.demo.dao.EmployeeDAO;
import com.demo.bean.Employee;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Employee模块的DAO层（数据层）的具体实现类，对EmployeeDAO接口中定义的增删改查等抽象方法作出具体的功能实现
 */
public class EmployeeDAOImpl implements EmployeeDAO {

    //@Override
    public void add(Employee vo) {
        String sql = "insert into `t_employee` (`employee_name`,`employee_sex`,`employee_phone`,`employee_dept`,`employee_job`,`employee_no`,`employee_age`,`employee_education`,`employee_intime`,`employee_text`) values(?,?,?,?,?,?,?,?,?,?)";
        try {
            Connection c = Util.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);
            
            ps.setString(1, vo.getEmployeeName());
            ps.setBoolean(2, vo.getEmployeeSex() != null ? vo.getEmployeeSex() : false);
            ps.setString(3, vo.getEmployeePhone());
            ps.setString(4, vo.getEmployeeDept());
            ps.setString(5, vo.getEmployeeJob());
            ps.setString(6, vo.getEmployeeNo());
            ps.setString(7, vo.getEmployeeAge());
            ps.setString(8, vo.getEmployeeEducation());
            ps.setString(9, vo.getEmployeeIntime());
            ps.setString(10, vo.getEmployeeText());
            ps.execute();
            ps.close();
            c.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //@Override
    public void update(Employee vo) {
        String sql = "update `t_employee` set `employee_name` = ? ,`employee_sex` = ? ,`employee_phone` = ? ,`employee_dept` = ? ,`employee_job` = ? ,`employee_no` = ? ,`employee_age` = ? ,`employee_education` = ? ,`employee_intime` = ? ,`employee_text` = ?  where `id` = ?";
        try {
            Connection c = Util.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);
            
            ps.setString(1, vo.getEmployeeName());
            ps.setBoolean(2, vo.getEmployeeSex() != null ? vo.getEmployeeSex() : false);
            ps.setString(3, vo.getEmployeePhone());
            ps.setString(4, vo.getEmployeeDept());
            ps.setString(5, vo.getEmployeeJob());
            ps.setString(6, vo.getEmployeeNo());
            ps.setString(7, vo.getEmployeeAge());
            ps.setString(8, vo.getEmployeeEducation());
            ps.setString(9, vo.getEmployeeIntime());
            ps.setString(10, vo.getEmployeeText());
            ps.setLong(11, vo.getId());
            ps.execute();
            ps.close();
            c.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //@Override
    public boolean delete(long id) {
        try {
            Connection c = Util.getConnection();
            Statement s = c.createStatement();
            String sql = "delete from `t_employee` where id = " + id;
            s.execute(sql);
            s.close();
            c.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    //@Override
    public Employee get(Serializable id) {
        Employee vo = null;
        try {
            Connection c = Util.getConnection();
            Statement s = c.createStatement();
            String sql = "select * from `t_employee` where id = " + id;
            ResultSet rs = s.executeQuery(sql);
            if (rs.next()) {
                vo = new Employee();
                vo.setId(rs.getLong("id"));
                vo.setEmployeeName(rs.getString("employee_name"));
                vo.setEmployeeSex(rs.getBoolean("employee_sex"));
                vo.setEmployeePhone(rs.getString("employee_phone"));
                vo.setEmployeeDept(rs.getString("employee_dept"));
                vo.setEmployeeJob(rs.getString("employee_job"));
                vo.setEmployeeNo(rs.getString("employee_no"));
                vo.setEmployeeAge(rs.getString("employee_age"));
                vo.setEmployeeEducation(rs.getString("employee_education"));
                vo.setEmployeeIntime(rs.getString("employee_intime"));
                vo.setEmployeeText(rs.getString("employee_text"));
            }
            c.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return vo;
    }

    //@Override
    public Map<String, Object> list(Map<String, Object> params) {
        List<Employee> list = new ArrayList();
        int totalCount = 0;
        String condition = "";
        String sqlList;
        if (params.get("searchColumn") != null && !"".equals(params.get("searchColumn"))) {
            condition += " and `" + params.get("searchColumn") + "` like '%" + params.get("keyword") + "%'";
        }
        try {
            Connection c = Util.getConnection();
            PreparedStatement ps;
            ResultSet rs;
            String limit = (params.get("startIndex") != null && params.get("pageSize") != null) ? " limit " + params.get("startIndex") + "," + params.get("pageSize") : "";
                sqlList = "select * from `t_employee` where 1=1 " + condition + " order by id asc " + limit + ";";
                ps = c.prepareStatement(sqlList);
                rs = ps.executeQuery();
                while (rs.next()) {
                    Employee vo = new Employee();
                    vo.setId(rs.getLong("id"));
                    vo.setEmployeeName(rs.getString("employee_name"));
                    vo.setEmployeeSex(rs.getBoolean("employee_sex"));
                    vo.setEmployeePhone(rs.getString("employee_phone"));
                    vo.setEmployeeDept(rs.getString("employee_dept"));
                    vo.setEmployeeJob(rs.getString("employee_job"));
                    vo.setEmployeeNo(rs.getString("employee_no"));
                    vo.setEmployeeAge(rs.getString("employee_age"));
                    vo.setEmployeeEducation(rs.getString("employee_education"));
                    vo.setEmployeeIntime(rs.getString("employee_intime"));
                    vo.setEmployeeText(rs.getString("employee_text"));
                    list.add(vo);
                }
            String sqlCount = "select count(*) from `t_employee` where 1=1 " + condition;
            ps = c.prepareStatement(sqlCount);
            rs = ps.executeQuery();
            if (rs.next()) {
                totalCount = rs.getInt(1);
            }
            rs.close();
            ps.close();
            c.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Map<String, Object> result = new HashMap();
        result.put("list", list);
        result.put("totalCount", totalCount);
        return result;
    }
}
